# from __future__ import unicode_literals
import os
import json
import numpy as np
import pandas as pd
import datetime
from env import ROOT_PATH
from utils import lastday_of_lastmonth
from form_format import adjust_format
from final_format import combine_forms

with open(f'{ROOT_PATH}\\年度参数.json', encoding='utf-8') as f:
    param_year = json.load(f)

with open(f'{ROOT_PATH}\\月度参数.json', encoding='utf-8') as f:
    param_mon = json.load(f)

def cal_stat(df,_type):
  case_bytype = df[[_type,'理赔总费用','减损金额']]

  case_bytype = pd.concat([case_bytype,pd.DataFrame(np.ones(case_bytype.shape[0]),case_bytype.index)],axis=1)
  stat_bytype = case_bytype.groupby(_type).sum()
  stat_bytype = stat_bytype.rename(columns={0: "出险单数"})
  stat_bytype['出险占比'] = stat_bytype['出险单数']/case_bytype.shape[0]
  stat_bytype['案均金额'] = stat_bytype['理赔总费用']/stat_bytype['出险单数']
   
  # 按照出险单数降序排列
  stat_bytype = stat_bytype.sort_values(by='出险单数', ascending=False)

  # 添加合计行
  total_row = pd.DataFrame(stat_bytype.sum(numeric_only=True)).T
  total_row[_type] = '合计'
  total_row = total_row.rename(columns={0: "出险单数"})
  total_row = total_row.set_index(_type)
  total_row.loc['合计','案均金额'] = total_row.loc['合计','理赔总费用'] / total_row.loc['合计','出险单数']
  stat_bytype = pd.concat([stat_bytype, total_row])  

  # 按照指定顺序输出列
  stat_bytype = stat_bytype.reindex(columns=['出险单数', '理赔总费用', '案均金额', '减损金额', '出险占比'])

  return stat_bytype

def stats_all(df,type_list,rawdata):
    '''根据某类别统计'''
    stats = {}

    for bytype in type_list:
      # bytype = '品牌_清洗' #根据品牌 
      # bytype = '出单经销店_中升' #根据门店
      stats[bytype] = cal_stat(df,bytype)

    '''各类品牌mask'''
    for brand in list(rawdata['品牌_清洗'].unique()):
      brand_mask = rawdata['品牌_清洗'] == brand
      df_brand = df.loc[brand_mask]
      stats[brand] = cal_stat(df_brand,'出单经销店_中升')

    return stats

def write_excel(save_path,result_dict):
    with pd.ExcelWriter(save_path, engine='openpyxl') as writer:
        for k, v in result_dict.items():
            v.to_excel(writer, sheet_name=k, startrow=1, startcol=1)

if __name__ == '__main__':
  
  # # 输入统计开始日期=====================================================================
  # start_date = '2024-01-01'#input('请输入统计开始日期 (格式YYYY-MM-DD): ')
  
  # # 输入统计截止日期=====================================================================
  # end_date = '2024-09-01'
  end_date = input('请输入当前(执行统计任务)日期 (格式YYYY-MM-DD): ')

  cur_yr = end_date.split('-')[0]
  cur_mon = end_date.split('-')[1]
  if cur_mon == '01':
     yr = str(int(cur_yr)-1)
  else: yr = cur_yr
  
  # Load raw data
  data_path = os.path.join(ROOT_PATH,f'data_raw\\{yr}')
  data_dict = {}

  for f in list(os.listdir(data_path)):
    file_date = f[:-5]
    if file_date < end_date:
        filepath = os.path.join(data_path,f)
        xls = pd.ExcelFile(filepath)
        data_dict[file_date] = pd.read_excel(xls, 'Sheet1', index_col=0)
  # Specially for cur_mon '02', read last-year '12' raw data for '01'report
  if cur_mon == '02':
     lastyr = str(int(cur_yr)-1)
     lastyr_data_path = os.path.join(ROOT_PATH,f'data_raw\\{lastyr}\\{lastyr}-12-31.xlsx')
     xls = pd.ExcelFile(lastyr_data_path)
     data_dict[f'{lastyr}-12-31'] = pd.read_excel(xls, 'Sheet1', index_col=0)

  key_list = sorted(data_dict)#打印已读月度原始数据 TODO check for 1月report
  print(f'{key_list} 月度原始数据读入完成')
  
  # TODO 1report
  if cur_mon == '02':
     data_raw = data_dict[key_list[-1]]
  else: 
    data_raw = pd.concat(data_dict,ignore_index=True)
  data_raw['出单经销店_中升'] = data_raw['出单经销店_中升'].replace(np.nan,'空白')
  
  case_dict = {}
  for status in data_raw['数据状态(计算)'].unique():
    status_mask = data_raw['数据状态(计算)'] == status  #['同意维修', '已拒赔', '已销案']
    case_dict[status] = data_raw.loc[status_mask]
  
  summary = {}
  lastest_key = sorted(data_dict)[-1]


  # 1. '已提交审核案件数' = '同意维修' + '已拒赔'
  df = pd.concat([case_dict['同意维修'],case_dict['已拒赔']])
  param_mon['统计结束日期'][lastest_key]['已提交审核案件数'] = df.shape[0]

  types = ['品牌_清洗', '出单经销店_中升']
  stats_check = stats_all(df,types,data_raw)
  # OUTPUT 汇总表1,2
  top_store = stats_check['出单经销店_中升'][['出险单数','出险占比']].head(20)  #已提交审核案件中按案件数量分，占比前20名的门店清单及所占比例
  top_brand = stats_check['品牌_清洗'][['出险单数','出险占比']].head(5)

  summary['汇总表1'] = top_store
  summary['汇总表2'] = top_brand


  # 2. OUTPUT '已拒赔' 汇总表5,6
  df = case_dict['已拒赔'].copy()
  param_mon['统计结束日期'][lastest_key]['拒赔'] = df.shape[0]
  types = ['出单经销店_中升']

  stats_rej = stats_all(df,types,data_raw)
  output_6 = stats_rej['出单经销店_中升'][['出险单数','出险占比']].head(5)
  output_6.columns = ['拒赔件数','拒赔案件占比']

  summary['汇总表5'] = pd.DataFrame(pd.Series({'拒赔':param_mon['统计结束日期'][lastest_key]['拒赔']})).T
  
  summary['汇总表6'] = output_6


  # 3. '同意维修'
  filter = '同意维修'
  df = case_dict[filter].copy()
  types = ['品牌_清洗', '出单经销店_中升']

  stats_acpt = stats_all(df,types,data_raw)

  result_path = os.path.join(ROOT_PATH,f'results/{end_date}')
  if not os.path.exists(result_path):
     os.makedirs(result_path)

  # Specify the Excel file name
  res_name = f'中升集团理赔统计-{filter}.xlsx'
  stat_path = os.path.join(result_path,res_name)

  write_excel(stat_path,stats_acpt)
  adjust_format(stat_path)
  
  # 特别地，对上月做单月月度统计
  first, last = lastday_of_lastmonth(datetime.datetime.strptime(end_date,'%Y-%m-%d'))
  mon_mask = (df['电话接案日期']>=first) & (df['电话接案日期']<=last)
  df_mon = df.loc[mon_mask]
  stats_lastmon = stats_all(df_mon,types,data_raw)

  param_mon['统计结束日期'][lastest_key]['案均金额'] = stats_lastmon['品牌_清洗'].loc['合计','案均金额'].round()

  accum_ave = int(stats_acpt['品牌_清洗'].loc['合计','案均金额'].round())
  param_mon['统计结束日期'][lastest_key]['本年度累计案均'] = accum_ave

  # 汇总表3
  yr = int(lastest_key[:4])
  last_yr = str(yr-1)

  # 同比
  YoY_diff = param_mon['统计结束日期'][lastest_key]['本年度累计案均'] - param_year[last_yr] ['案均金额']
  YoY_pct = param_mon['统计结束日期'][lastest_key]['本年度累计案均'] / param_year[last_yr] ['案均金额']
  # 环比
  prev_key = sorted(data_dict)[-2] # TODO 一月报表较上年12月比较
  MoM_diff = param_mon['统计结束日期'][lastest_key]['案均金额'] - param_mon['统计结束日期'][prev_key]['案均金额']
  MoM_pct = param_mon['统计结束日期'][lastest_key]['案均金额'] / param_mon['统计结束日期'][prev_key]['案均金额']

  change = {}
  change['与上年同比'] = {'金额变化':YoY_diff, '比例':YoY_pct}

  char_lastmon = lastest_key.split('-')[1]
  char_prevmon = prev_key.split('-')[1]
  change[f'与上月({char_lastmon}月/{char_prevmon}月)环比'] = {'金额变化':MoM_diff, '比例':MoM_pct}
  
  # OUTPUT3
  df_change = pd.DataFrame(change).T
  summary['汇总表3_1'] = df_change


  # 月度数据输出
  with open(os.path.join(ROOT_PATH,f'月度参数.json'), 'w', encoding='utf8') as f:
    f.write(json.dumps(param_mon, ensure_ascii=False, default=lambda o: o.__dict__, sort_keys=True, indent=2))
  
  # mon_list = ['0'+str(mon) for mon in range(1,10)] + ['10','11','12']
  mon_summary = {}

  for key in key_list:
     mon_summary[key[:-3]] = param_mon['统计结束日期'][key]['案均金额']
  if cur_mon == '02':
     accum_start = key_list[-1].split('-')[0]+'年1-'
  else: accum_start = key_list[0].split('-')[0]+'年1-'
  accum_end = key_list[-1].split('-')[1][1]
  mon_summary[f'{accum_start}{accum_end}月'] = param_mon['统计结束日期'][key_list[-1]]['本年度累计案均']
  mon_summary[f'{last_yr}年'] = param_year[last_yr]['案均金额']

  mon_summary_df = pd.DataFrame([mon_summary]).T.reset_index()
  mon_summary_df.columns = ['月份','案均金额']

  summary['汇总表3_2'] = mon_summary_df.tail(4)


  # TODO OUTPUT4，缺少上年案均数据 hardcode
  stats_store_2023 = pd.read_csv(os.path.join(ROOT_PATH, 'draft\stats_store_2023.csv'),index_col=0)
  stats_brand_2023 = pd.read_csv(os.path.join(ROOT_PATH, 'draft\stats_brand_2023.csv'),index_col=0)

  YoY_store = (stats_acpt['出单经销店_中升']['案均金额'] / stats_store_2023['案均金额']).sort_values(ascending=False).head(5)
  YoY_brand = (stats_acpt['品牌_清洗']['案均金额'] / stats_brand_2023['案均金额']).sort_values(ascending=False).head(5)

  # output_4 = pd.concat([YoY_brand,YoY_store],axis=0)
  summary['汇总表4_1'] = YoY_brand
  summary['汇总表4_2'] = YoY_store

  param_mon['统计结束日期'][lastest_key]['已销案'] = case_dict['已销案'].shape[0]
  output0 = pd.DataFrame([param_mon['统计结束日期'][lastest_key]])[['总报案数','已提交审核案件数','已销案','未处置案件数']].T.reset_index()
  # output0 = pd.DataFrame([param_mon['统计结束日期'][lastest_key]])[['总报案数','已提交审核案件数','未处置案件数']].T.reset_index()
  output0.columns = ['参数','件数']
  summary['汇总表0'] = output0

  for k in sorted(summary.keys()):
     if not isinstance(summary[k].index[0], int):
      summary[k] = summary[k].reset_index()
  
  keys = list(summary.keys())
  keys.sort()
  summary_sorted = {i: summary[i] for i in keys} 
  file_path = os.path.join(result_path,'summary.xlsx')  # Use the actual path where the file is saved
  write_excel(file_path,summary_sorted)


  df_list = list(summary_sorted.values())

  output_file_path = os.path.join(result_path,'final.xlsx')
  combine_forms(file_path,output_file_path)







